﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Win32;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.ComponentModel;


namespace AlgoLogicMessenger
{
    //Interaction logic for ImportData.xaml
    public partial class ImportData : Page
    {
        public class RetrievedItems
        {
            public string Question { get; set; }
            public string Category { get; set; }
            public string Answer { get; set; }
            public string Image { get; set; }
            public string OptA { get; set; }
            public string OptB { get; set; }
            public string OptC { get; set; }
            public string OptD { get; set; }
            public string Min { get; set; }
            public string Sec { get; set; }
        }

        public ImportData()
        {
            InitializeComponent();
        }

        private void BtnBack_Click(object sender, RoutedEventArgs e)
        {
            NavigationService nav = NavigationService.GetNavigationService(this);
            nav.Navigate(new System.Uri("ManageQuizModule.xaml", UriKind.RelativeOrAbsolute));
        }

        private void btnOpen_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                OpenFileDialog dlg = new OpenFileDialog();
                dlg.DefaultExt = ".xlsx";
                dlg.Filter = "Excel Documents (*.xlsx);(.xls)|*.xlsx;*.xls"; ;

                dlg.Multiselect = true; //select multiple files

                // Get the selected file name and display in a TextBox
                excelBox.Clear();
                if (dlg.ShowDialog() != null)
                {
                    // Open document
                    excelBox.Text = dlg.FileName;
                    this.dgImport.DataContext = dataGet.Table;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Please select an excel file.");
            }
        }

        public DataView dataGet
        {
            get
            {
                Excel.Application excelApp = new Excel.Application();
                Excel.Workbook workbook;
                Excel.Worksheet worksheet;
                Excel.Range range;

                workbook = excelApp.Workbooks.Open(excelBox.Text);
                worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(2);

                int column = 0;
                int row = 0;

                range = worksheet.UsedRange;
                DataTable dt = new DataTable();

                for (column = 1; column <= range.Columns.Count; column++)
                {
                    dt.Columns.Add((range.Cells[1, column] as Excel.Range).Value2.ToString());
                }

                for (row = 2; row <= range.Rows.Count; row++)
                {
                    DataRow dr = dt.NewRow();
                    for (column = 1; column <= range.Columns.Count; column++)
                    {
                        dr[column - 1] = (range.Cells[row, column] as Excel.Range).Value2.ToString();
                    }
                    dt.Rows.Add(dr);
                    dt.AcceptChanges();
                }

                workbook.Close(true, Missing.Value, Missing.Value);
                excelApp.Quit();
                return dt.DefaultView;
            }
        }

        public static DataTable objectListToDataTable(List<RetrievedItems> objList)
        {
            DataTable dt = new DataTable();
            dt.TableName = "ObjectList";
            foreach (PropertyInfo property in objList[0].GetType().GetProperties())
            {
                dt.Columns.Add(new DataColumn(property.Name, property.PropertyType));
            }

            foreach (RetrievedItems item in objList)
            {
                DataRow newRow = dt.NewRow();
                foreach (PropertyInfo property in item.GetType().GetProperties())
                {
                    newRow[property.Name] = item.GetType().GetProperty(property.Name).GetValue(item, null);
                }
                dt.Rows.Add(newRow);
            }
            return dt;
        }

        private void BtnImport_Click(object sender, RoutedEventArgs e)
        {
            const string message = "Are you sure to import the selected questions into the database?";
            const string caption = "Importing questions";
            MessageBoxResult result = MessageBox.Show(message, caption, MessageBoxButton.YesNo, MessageBoxImage.Question);

            if (result == MessageBoxResult.Yes)
            {
                if (excelBox.Text == "")
                {
                    if (dgImport.SelectedItems.Count == 0)

                        MessageBox.Show("Please Select a Question!", "Notification", MessageBoxButton.OK, MessageBoxImage.Information);

                    else
                        MessageBox.Show("Unable to import existing questions");
                }

                else
                {
                    if (dgImport.SelectedItems.Count > 0)
                    {
                        string exists = null;
                        for (int j = 0; j < dgImport.SelectedItems.Count; j++)
                        {
                            var path = excelBox.Text;
                            FileInfo file = new FileInfo(path);
                            string fn = file.Name;

                            var row = ((System.Data.DataRowView)dgImport.SelectedItems[j]).Row;
                            DataRowView dv = new DataRowView(row);
                            string qns = dv.qns.ToString();

                            var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");
                            conn.Open();
                            var comm = new SqlCommand();
                            comm.Connection = conn;
                            comm.CommandText = " if exists(select * from export where Question=@Question) select 'True' as 'Exists' else select 'False' as 'Exists'";
                            comm.Parameters.AddWithValue("@Question", qns);
                            comm.ExecuteNonQuery();
                            SqlDataReader dr = comm.ExecuteReader();
                            if (dr.HasRows == true)
                            {
                                while (dr.Read())
                                {
                                    exists = (string)dr["Exists"];
                                }
                                if (exists == "True")
                                {
                                    MessageBox.Show("Unable to import existing questions");
                                    j = dgImport.SelectedItems.Count;
                                    dr.Close();
                                    conn.Close();
                                }
                            }
                            dr.Close();
                            conn.Close();
                        }

                        if (exists == "False")
                        {
                            for (int j = 0; j < dgImport.SelectedItems.Count; j++)
                            {
                                var path = excelBox.Text;
                                FileInfo file = new FileInfo(path);
                                string fn = file.Name;

                                var row = ((System.Data.DataRowView)dgImport.SelectedItems[j]).Row;
                                DataRowView dv = new DataRowView(row);
                                string qns = dv.qns.ToString();
                                string answer = dv.answer.ToString();
                                string picpath = dv.picpath.ToString();
                                string a = dv.a.ToString();
                                string b = dv.b.ToString();
                                string c = dv.c.ToString();
                                string d = dv.d.ToString();
                                string m = dv.m.ToString();
                                string s = dv.s.ToString();
                                string cat = dv.cat.ToString();

                                try
                                {
                                    var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");
                                    conn.Open();
                                    var comm = new SqlCommand();
                                    comm.Connection = conn;
                                    comm.CommandText = " insert into Export(Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec) values(@Question, @Category, @FileName, @Answer, @Image, @OptA, @OptB, @OptC, @OptD, @Min, @Sec)";
                                    comm.Parameters.AddWithValue("@Question", qns);
                                    comm.Parameters.AddWithValue("@Category", cat);
                                    comm.Parameters.AddWithValue("@FileName", fn);
                                    comm.Parameters.AddWithValue("@Answer", answer);
                                    comm.Parameters.AddWithValue("@Image", picpath);
                                    comm.Parameters.AddWithValue("@OptA", a);
                                    comm.Parameters.AddWithValue("@OptB", b);
                                    comm.Parameters.AddWithValue("@OptC", c);
                                    comm.Parameters.AddWithValue("@OptD", d);
                                    comm.Parameters.AddWithValue("@Min", m);
                                    comm.Parameters.AddWithValue("@Sec", s);
                                    comm.ExecuteNonQuery();
                                    conn.Close();
                                }
                                catch (Exception ex)
                                {
                                    MessageBox.Show("Unable to establish connection to database");
                                }
                            }
                            MessageBox.Show("Questions are imported Successfully!", "Notification", MessageBoxButton.OK, MessageBoxImage.Information);
                            dgImport.DataContext = null;
                            excelBox.Text = "";
                        }
                    }

                    else
                    {
                        MessageBox.Show("Please Select a Question!", "Notification", MessageBoxButton.OK, MessageBoxImage.Information);
                    }
                }
            }
        }

        private void BtnDeleteAll_Click(object sender, RoutedEventArgs e)
        {
            const string message = "Are you sure to delete all existing questions in the database?";
            const string caption = "Deleting question";
            MessageBoxResult result = MessageBox.Show(message, caption, MessageBoxButton.YesNo, MessageBoxImage.Question);

            if (result == MessageBoxResult.Yes)
            {
                var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");
                var comm = new SqlCommand();
                comm.Connection = conn;

                try
                {
                    conn.Open();
                    comm.CommandText = "select TOP 1 QuizNum from export";
                    comm.ExecuteNonQuery();
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        dr.Close();
                        try
                        {
                            comm.CommandText = "delete from export DBCC CHECKIDENT (export, RESEED, 0)";
                            comm.ExecuteNonQuery();
                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Unable to establish connection to the database");
                        }

                        finally
                        {
                            MessageBox.Show("Deleted all existing questions in the database successfully.");
                            dgImport.DataContext = null;
                            excelBox.Text = "";
                        }
                    }

                    else
                        MessageBox.Show("There are no existing questions in the database to be deleted.");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Unable to establish connection to the database");
                }
            }
        }

        private void Page_PreviewKeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.F5 || e.Key == Key.Back)
            {
                e.Handled = true;
            }
        }

        private void BtnRetrieve_Click(object sender, RoutedEventArgs e)
        {
            var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");
            var comm = new SqlCommand();
            comm.Connection = conn;
            List<RetrievedItems> riList = new List<RetrievedItems>();
            try
            {
                conn.Open();
                comm.CommandText = "select Question, Category, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec from export";
                comm.ExecuteNonQuery();
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    RetrievedItems ri = new RetrievedItems();
                    ri.Question = (string)dr["Question"];
                    ri.Category = (string)dr["Category"];
                    ri.Answer = (string)dr["Answer"];
                    ri.Image = (string)dr["Image"];
                    ri.OptA = (string)dr["OptA"];
                    ri.OptB = (string)dr["OptB"];
                    ri.OptC = (string)dr["OptC"];
                    ri.OptD = (string)dr["OptD"];
                    ri.Min = Convert.ToString((int)dr["Min"]);
                    ri.Sec = Convert.ToString((int)dr["Sec"]);
                    riList.Add(ri);
                }
                excelBox.Text = "";
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Unable to establish connection to the database.");
            }

            finally
            {
                if (riList.Count != 0)
                {
                    DataTable table = objectListToDataTable(riList);
                    dgImport.DataContext = table;
                }

                else
                {
                    MessageBox.Show("There are no existing questions in the database.");
                    dgImport.DataContext = null;
                    excelBox.Text = "";
                }
            }
        }
    }
}